1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmBilling1
5 Dim st2 As String
6
7 Sub Reset()
8 txtCID.Text = ""
9 txtRemarks.Text = ""
10 txtCustomerName.Text = ""
11 txtAmount.Text = ""
12 txtCostPrice.Text = ""
13 txtCustomerID.Text = ""
14 txtDiscountAmount.Text = ""
15 txtDiscountPer.Text = ""
16 txtMargin.Text = ""
17 txtInvoiceNo.Text = ""
18 txtProductCode.Text = ""
19 txtProductName.Text = ""
20 txtQty.Text = ""
21 txtSellingPrice.Text = ""
22 txtTotalAmount.Text = ""
23 txtTotalQty.Text = ""
24 txtVAT.Text = ""
25 txtVATAmount.Text = ""
26 txtGrandTotal.Text = ""
27 txtTotalPayment.Text = ""
28 txtPaymentDue.Text = ""
29 txtServiceCode.Text = ""
30 txtRepairCharges.Text = ""
31 txtUpfront.Text = ""
32 txtProductCharges.Text = 0
33 txtServiceTaxPer.Text = ""
34 txtServiceTaxAmount.Text = ""
35 dtpInvoiceDate.Text = Today
36 btnDelete.Enabled = False
37 btnUpdate.Enabled = False
38 btnSave.Enabled = True
39 btnRemove.Enabled = False
40 btnAdd.Enabled = True
41 btnRemove1.Enabled = False
42 btnAdd1.Enabled = True
43 btnPrint.Enabled = False
44 txtContactNo.Text = ""
45 auto()
46 lblSet.Text = "Allowed"
47 DataGridView1.Rows.Clear()
48 DataGridView2.Rows.Clear()
49 Clear()
50 Clear1()
51 End Sub
52 Private Function GenerateID() As String
53 con = New SqlConnection(cs)
54 Dim value As String = "0000"
55 Try
56 ' Fetch the latest ID from the database
57 con.Open()
58 cmd = New SqlCommand("SELECT TOP 1 Inv_ID FROM InvoiceInfo1 ORDER BY Inv_ID DESC", con)
59 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
60 If rdr.HasRows Then
61 rdr.Read()
62 value = rdr.Item("Inv_ID")
63 End If
64 rdr.Close()
65 ' Increase the ID by 1
66 value += 1
67 ' Because incrementing a string with an integer removes 0's
68 ' we need to replace them. If necessary.
69 If value <= 9 Then 'Value is between 0 and 10
70 value = "000" & value
71 ElseIf value <= 99 Then 'Value is between 9 and 100
72 value = "00" & value
73 ElseIf value <= 999 Then 'Value is between 999 and 1000
74 value = "0" & value
75 End If
76 Catch ex As Exception
77 ' If an error occurs, check the connection state and close it if necessary.
78 If con.State = ConnectionState.Open Then
79 con.Close()
80 End If
81 value = "0000"
82 End Try
83 Return value
84 End Function
85 Sub auto()
86 Try
87 txtID.Text = GenerateID()
88 txtInvoiceNo.Text = "INV-" + GenerateID()
89 Catch ex As Exception
90 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
91 End Try
92 End Sub
93 Private Sub btnSelect_Click(sender As System.Object, e As System.EventArgs) Handles btnSelect.Click
94 frmServicesRecord1.Reset()
95 frmServicesRecord1.ShowDialog()
96 End Sub
97
98 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
99 Me.Close()
100 End Sub
101
102 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles btnSelectionInv.Click
103 frmCurrentStock.lblSet.Text = "Billing1"
104 frmCurrentStock.Reset()
105 frmCurrentStock.ShowDialog()
106 End Sub
107 Sub Compute()
108 Dim num1, num2, num3, num4, num5 As Double
109 num1 = CDbl(Val(txtQty.Text) * Val(txtSellingPrice.Text))
110 num1 = Math.Round(num1, 2)
111 txtAmount.Text = num1
112 num2 = CDbl((Val(txtAmount.Text) * Val(txtDiscountPer.Text)) / 100)
113 num2 = Math.Round(num2, 2)
114 txtDiscountAmount.Text = num2
115 num3 = Val(txtAmount.Text) - Val(txtDiscountAmount.Text)
116 num4 = CDbl((Val(txtVAT.Text) * Val(num3)) / 100)
117 num4 = Math.Round(num4, 2)
118 txtVATAmount.Text = num4
119 num5 = CDbl(Val(txtAmount.Text) + Val(txtVATAmount.Text) - Val(txtDiscountAmount.Text))
120 num5 = Math.Round(num5, 2)
121 txtTotalAmount.Text = num5
122 End Sub
123
124 Private Sub txtQty_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtQty.TextChanged
125 Compute()
126 End Sub
127
128 Private Sub txtQty_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtQty.KeyPress
129 If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(8) Then
130 e.Handled = True
131 End If
132 End Sub
133 Public Function GrandTotal() As Double
134 Dim sum As Double = 0
135 Try
136 For Each r As DataGridViewRow In Me.DataGridView1.Rows
137 sum = sum + r.Cells(11).Value
138 Next
139 Catch ex As Exception
140 MsgBox(ex.Message)
141 End Try
142 Return sum
143 End Function
144 Public Function TotalPayment() As Double
145 Dim sum As Double = 0
146 Dim sum1 As Double
147 Try
148 For Each r As DataGridViewRow In Me.DataGridView2.Rows
149 sum = sum + r.Cells(1).Value
150 Next
151 sum1 = sum + Val(txtUpfront.Text)
152 Catch ex As Exception
153 MsgBox(ex.Message)
154 End Try
155 Return sum1
156 End Function
157 Sub Print()
158 Try
159 Cursor = Cursors.WaitCursor
160 Timer1.Enabled = True
161 Dim rpt As New rptInvoice1 'The report you created.
162 Dim myConnection As SqlConnection
163 Dim MyCommand, MyCommand1 As New SqlCommand()
164 Dim myDA, myDA1 As New SqlDataAdapter()
165 Dim myDS As New DataSet 'The DataSet you created.
166 myConnection = New SqlConnection(cs)
167 MyCommand.Connection = myConnection
168 MyCommand1.Connection = myConnection
169 MyCommand.CommandText = "SELECT Service.S_ID, Service.ServiceCode, Service.ServiceType, Service.ServiceCreationDate, Service.ItemDescription, Service.ProblemDescription, Service.ChargesQuote,Service.AdvanceDeposit, Service.EstimatedRepairDate, Service.Remarks, Service.Status, Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City,Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks AS Expr2, Customer.Photo, InvoiceInfo1.Inv_ID, InvoiceInfo1.InvoiceNo, InvoiceInfo1.InvoiceDate,InvoiceInfo1.ServiceID, InvoiceInfo1.RepairCharges, InvoiceInfo1.Upfront, InvoiceInfo1.ProductCharges, InvoiceInfo1.ServiceTaxPer, InvoiceInfo1.ServiceTax, InvoiceInfo1.GrandTotal, InvoiceInfo1.TotalPaid,InvoiceInfo1.Balance, InvoiceInfo1.Remarks AS Expr3, Invoice1_Product.Ipo_ID, Invoice1_Product.InvoiceID, Invoice1_Product.ProductID, Invoice1_Product.CostPrice, Invoice1_Product.SellingPrice,Invoice1_Product.Margin, Invoice1_Product.Qty, Invoice1_Product.Amount, Invoice1_Product.DiscountPer, Invoice1_Product.Discount, Invoice1_Product.VATPer, Invoice1_Product.VAT,Invoice1_Product.TotalAmount, Product.PID, Product.ProductCode, Product.ProductName, Product.SubCategoryID, Product.Description FROM Service INNER JOIN Customer ON Service.CustomerID = Customer.ID INNER JOIN InvoiceInfo1 ON Service.S_ID = InvoiceInfo1.ServiceID INNER JOIN Invoice1_Product ON InvoiceInfo1.Inv_ID = Invoice1_Product.InvoiceID INNER JOIN Product ON Invoice1_Product.ProductID = Product.PID where InvoiceInfo1.Invoiceno=@d1"
170 MyCommand.Parameters.AddWithValue("@d1", txtInvoiceNo.Text)
171 MyCommand1.CommandText = "SELECT * from Company"
172 MyCommand.CommandType = CommandType.Text
173 MyCommand1.CommandType = CommandType.Text
174 myDA.SelectCommand = MyCommand
175 myDA1.SelectCommand = MyCommand1
176 myDA.Fill(myDS, "InvoiceInfo1")
177 myDA.Fill(myDS, "Invoice1_Product")
178 myDA.Fill(myDS, "Service")
179 myDA.Fill(myDS, "Customer")
180 myDA.Fill(myDS, "Product")
181 myDA1.Fill(myDS, "Company")
182 rpt.SetDataSource(myDS)
183 rpt.SetParameterValue("p1", txtCustomerID.Text)
184 rpt.SetParameterValue("p2", Today)
185 frmReport.CrystalReportViewer1.ReportSource = rpt
186 frmReport.ShowDialog()
187 Catch ex As Exception
188 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
189 End Try
190
191 End Sub
192 Private Sub frmBilling_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
193
194 End Sub
195
196 Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
197 Try
198 If txtProductCode.Text = "" Then
199 MessageBox.Show("Please retrieve product code", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
200 txtProductCode.Focus()
201 Exit Sub
202 End If
203 If txtQty.Text = "" Then
204 MessageBox.Show("Please enter quantity", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
205 txtQty.Focus()
206 Exit Sub
207 End If
208 If txtQty.Text = 0 Then
209 MessageBox.Show("Quantity can not be zero", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
210 txtQty.Focus()
211 Exit Sub
212 End If
213 If DataGridView1.Rows.Count = 0 Then
214 DataGridView1.Rows.Add(txtProductCode.Text, txtProductName.Text, txtCostPrice.Text, txtSellingPrice.Text, txtMargin.Text, txtQty.Text, txtAmount.Text, txtDiscountPer.Text, txtDiscountAmount.Text, txtVAT.Text, txtVATAmount.Text, txtTotalAmount.Text, txtProductID.Text)
215 Dim k As Double = 0
216 k = GrandTotal()
217 k = Math.Round(k, 2)
218 txtProductCharges.Text = k
219 Compute1()
220 Clear()
221 Exit Sub
222 End If
223 For Each r As DataGridViewRow In Me.DataGridView1.Rows
224 If r.Cells(0).Value = txtProductCode.Text Then
225 r.Cells(0).Value = txtProductCode.Text
226 r.Cells(1).Value = txtProductName.Text
227 r.Cells(2).Value = txtCostPrice.Text
228 r.Cells(3).Value = txtSellingPrice.Text
229 r.Cells(4).Value = txtMargin.Text
230 r.Cells(5).Value = Val(r.Cells(5).Value) + Val(txtQty.Text)
231 r.Cells(6).Value = Val(r.Cells(6).Value) + Val(txtAmount.Text)
232 r.Cells(7).Value = Val(txtDiscountPer.Text)
233 r.Cells(8).Value = Val(r.Cells(8).Value) + Val(txtDiscountAmount.Text)
234 r.Cells(9).Value = Val(txtVAT.Text)
235 r.Cells(10).Value = Val(r.Cells(10).Value) + Val(txtVATAmount.Text)
236 r.Cells(11).Value = Val(r.Cells(11).Value) + Val(txtTotalAmount.Text)
237 r.Cells(12).Value = txtProductID.Text
238 Dim i As Double = 0
239 i = GrandTotal()
240 i = Math.Round(i, 2)
241 txtProductCharges.Text = i
242 Compute1()
243 Clear()
244 Exit Sub
245 End If
246 Next
247 DataGridView1.Rows.Add(txtProductCode.Text, txtProductName.Text, txtCostPrice.Text, txtSellingPrice.Text, txtMargin.Text, txtQty.Text, txtAmount.Text, txtDiscountPer.Text, txtDiscountAmount.Text, txtVAT.Text, txtVATAmount.Text, txtTotalAmount.Text, txtProductID.Text)
248 Dim j As Double = 0
249 j = GrandTotal()
250 j = Math.Round(j, 2)
251 txtProductCharges.Text = j
252 Compute1()
253 Clear()
254 Catch ex As Exception
255 MsgBox(ex.Message)
256 End Try
257 End Sub
258 Sub Clear()
259 txtProductCode.Text = ""
260 txtProductName.Text = ""
261 txtCostPrice.Text = ""
262 txtSellingPrice.Text = ""
263 txtMargin.Text = ""
264 txtQty.Text = ""
265 txtAmount.Text = ""
266 txtDiscountPer.Text = ""
267 txtDiscountAmount.Text = ""
268 txtVAT.Text = ""
269 txtVATAmount.Text = ""
270 txtTotalAmount.Text = ""
271 btnAdd.Enabled = True
272 btnRemove.Enabled = False
273 btnListUpdate.Enabled = False
274 End Sub
275
276 Private Sub btnRemove_Click(sender As System.Object, e As System.EventArgs) Handles btnRemove.Click
277 Try
278 For Each row As DataGridViewRow In DataGridView1.SelectedRows
279 DataGridView1.Rows.Remove(row)
280 Next
281 Dim k As Double = 0
282 k = GrandTotal()
283 k = Math.Round(k, 2)
284 txtProductCharges.Text = k
285 Compute()
286 Compute1()
287 Clear()
288 Catch ex As Exception
289 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
290 End Try
291 End Sub
292
293 Private Sub DataGridView1_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
294
295 If (Me.DataGridView1.Rows.Count > 0) Then
296 If lblSet.Text = "Not Allowed" Then
297 btnRemove.Enabled = False
298 btnListUpdate.Enabled = False
299 Else
300 btnRemove.Enabled = True
301 btnListUpdate.Enabled = True
302 End If
303 Me.btnAdd.Enabled = False
304 Dim row As DataGridViewRow = Me.DataGridView1.SelectedRows.Item(0)
305 Me.txtProductCode.Text = (row.Cells.Item(0).Value)
306 Me.txtProductName.Text = (row.Cells.Item(1).Value)
307 Me.txtCostPrice.Text = (row.Cells.Item(2).Value)
308 Me.txtSellingPrice.Text = (row.Cells.Item(3).Value)
309 Me.txtMargin.Text = (row.Cells.Item(4).Value)
310 Me.txtQty.Text = (row.Cells.Item(5).Value)
311 Me.txtAmount.Text = (row.Cells.Item(6).Value)
312 Me.txtDiscountPer.Text = (row.Cells.Item(7).Value)
313 Me.txtDiscountAmount.Text = (row.Cells.Item(8).Value)
314 Me.txtVAT.Text = (row.Cells.Item(9).Value)
315 Me.txtVATAmount.Text = (row.Cells.Item(10).Value)
316 Me.txtTotalAmount.Text = (row.Cells.Item(11).Value)
317 Me.txtProductID.Text = (row.Cells.Item(12).Value)
318 End If
319 End Sub
320
321 Private Sub DataGridView1_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView1.RowPostPaint
322 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
323 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
324 If DataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
325 DataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
326 End If
327 Dim b As Brush = SystemBrushes.ControlText
328 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
329
330 End Sub
331
332 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
333 Try
334 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
335 DeleteRecord()
336 End If
337 Catch ex As Exception
338 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
339 End Try
340 End Sub
341 Private Sub DeleteRecord()
342
343 Try
344 Dim RowsAffected As Integer = 0
345 con = New SqlConnection(cs)
346 con.Open()
347 Dim cq As String = "delete from InvoiceInfo1 where Inv_ID=@d1"
348 cmd = New SqlCommand(cq)
349 cmd.Parameters.AddWithValue("@d1", txtID.Text)
350 cmd.Connection = con
351 RowsAffected = cmd.ExecuteNonQuery()
352 If RowsAffected > 0 Then
353 Dim st As String = "deleted the bill (Products + Service) having invoice no. '" & txtInvoiceNo.Text & "'"
354 LogFunc(lblUser.Text, st)
355 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
356 Reset()
357 Reset()
358 Else
359 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
360 Reset()
361 End If
362 If con.State = ConnectionState.Open Then
363 con.Close()
364
365 End If
366 Catch ex As Exception
367 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
368 End Try
369 End Sub
370 Sub Compute1()
371 Dim num1, num2, num3 As Double
372 num1 = CDbl((Val(txtRepairCharges.Text) * Val(txtServiceTaxPer.Text)) / 100)
373 num1 = Math.Round(num1, 2)
374 txtServiceTaxAmount.Text = num1
375 num2 = CDbl(Val(txtRepairCharges.Text) + Val(txtServiceTaxAmount.Text) + Val(txtProductCharges.Text))
376 num2 = Math.Round(num2, 2)
377 txtGrandTotal.Text = num2
378 num3 = Val(txtGrandTotal.Text) - Val(txtTotalPayment.Text)
379 num3 = Math.Round(num3, 2)
380 txtPaymentDue.Text = num3
381 End Sub
382 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
383 If Len(Trim(txtCustomerName.Text)) = 0 Then
384 MessageBox.Show("Please retrieve service details", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
385 Exit Sub
386 End If
387 If Len(Trim(txtRepairCharges.Text)) = 0 Then
388 MessageBox.Show("Please enter service charges", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
389 txtRepairCharges.Focus()
390 Exit Sub
391 End If
392 If Len(Trim(txtServiceTaxPer.Text)) = 0 Then
393 MessageBox.Show("Please enter service tax %", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
394 txtServiceTaxPer.Focus()
395 Exit Sub
396 End If
397 If DataGridView2.Rows.Count = 0 Then
398 MessageBox.Show("sorry no payment info added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
399 Exit Sub
400 End If
401 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
402 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
403 Exit Sub
404 End If
405 Try
406 con = New SqlConnection(cs)
407 con.Open()
408 Dim ctn As String = "select * from Company"
409 cmd = New SqlCommand(ctn)
410 cmd.Connection = con
411 rdr = cmd.ExecuteReader()
412
413 If Not rdr.Read() Then
414 MessageBox.Show("Add company profile first in master entry", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
415 If (rdr IsNot Nothing) Then
416 rdr.Close()
417 End If
418 Return
419 End If
420 For Each row As DataGridViewRow In DataGridView1.Rows
421 Dim con As New SqlConnection(cs)
422 con.Open()
423 Dim cmd As New SqlCommand("SELECT Qty from Temp_Stock where ProductID=@d1", con)
424 cmd.Parameters.AddWithValue("@d1", row.Cells(12).Value.ToString())
425 Dim da As New SqlDataAdapter(cmd)
426 Dim ds As DataSet = New DataSet()
427 da.Fill(ds)
428 If ds.Tables(0).Rows.Count > 0 Then
429 txtTotalQty.Text = ds.Tables(0).Rows(0)("Qty")
430 If CInt(Val(row.Cells(5).Value)) > Val(txtTotalQty.Text) Then
431 MessageBox.Show("added qty. to cart are more than" & vbCrLf & "available qty. of product code '" & row.Cells(0).Value.ToString() & "' and Product Name='" & row.Cells(1).Value & "'", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
432 Exit Sub
433 End If
434 End If
435 con.Close()
436 Next
437 Catch ex As Exception
438 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
439 End Try
440 Try
441 con = New SqlConnection(cs)
442 con.Open()
443 Dim cb As String = "insert into InvoiceInfo1( Inv_ID, InvoiceNo, InvoiceDate, ServiceID, RepairCharges, Upfront, ProductCharges, ServiceTaxPer, ServiceTax, GrandTotal, TotalPaid, Balance, Remarks) Values (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13)"
444 cmd = New SqlCommand(cb)
445 cmd.Parameters.AddWithValue("@d1", txtID.Text)
446 cmd.Parameters.AddWithValue("@d2", txtInvoiceNo.Text)
447 cmd.Parameters.AddWithValue("@d3", dtpInvoiceDate.Value.Date)
448 cmd.Parameters.AddWithValue("@d4", txtS_ID.Text)
449 cmd.Parameters.AddWithValue("@d5", txtRepairCharges.Text)
450 cmd.Parameters.AddWithValue("@d6", txtUpfront.Text)
451 cmd.Parameters.AddWithValue("@d7", txtProductCharges.Text)
452 cmd.Parameters.AddWithValue("@d8", txtServiceTaxPer.Text)
453 cmd.Parameters.AddWithValue("@d9", txtServiceTaxAmount.Text)
454 cmd.Parameters.AddWithValue("@d10", txtGrandTotal.Text)
455 cmd.Parameters.AddWithValue("@d11", txtTotalPayment.Text)
456 cmd.Parameters.AddWithValue("@d12", txtPaymentDue.Text)
457 cmd.Parameters.AddWithValue("@d13", txtRemarks.Text)
458 cmd.Connection = con
459 cmd.ExecuteReader()
460 con.Close()
461
462 If DataGridView1.Rows.Count = 0 Then
463 con = New SqlConnection(cs)
464 con.Open()
465 Dim cx As String = "insert into Invoice1_Product(InvoiceID, CostPrice, SellingPrice, Margin, Qty, Amount, DiscountPer, Discount, VATPer, VAT, TotalAmount,ProductID) VALUES (" & txtID.Text & " ,0,0,0,0,0,0,0,0,0,0,1)"
466 cmd = New SqlCommand(cx)
467 cmd.Connection = con
468 cmd.ExecuteNonQuery()
469 con.Close()
470 Else
471 con = New SqlConnection(cs)
472 con.Open()
473 Dim cb1 As String = "insert into Invoice1_Product(InvoiceID, CostPrice, SellingPrice, Margin, Qty, Amount, DiscountPer, Discount, VATPer, VAT, TotalAmount,ProductID) VALUES (" & txtID.Text & " ,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13,@d14)"
474 cmd = New SqlCommand(cb1)
475 cmd.Connection = con
476 ' Prepare command for repeated execution
477 cmd.Prepare()
478 ' Data to be inserted
479 For Each row As DataGridViewRow In DataGridView1.Rows
480 If Not row.IsNewRow Then
481 cmd.Parameters.AddWithValue("@d4", row.Cells(2).Value)
482 cmd.Parameters.AddWithValue("@d5", row.Cells(3).Value)
483 cmd.Parameters.AddWithValue("@d6", row.Cells(4).Value)
484 cmd.Parameters.AddWithValue("@d7", row.Cells(5).Value)
485 cmd.Parameters.AddWithValue("@d8", row.Cells(6).Value)
486 cmd.Parameters.AddWithValue("@d9", row.Cells(7).Value)
487 cmd.Parameters.AddWithValue("@d10", row.Cells(8).Value)
488 cmd.Parameters.AddWithValue("@d11", row.Cells(9).Value)
489 cmd.Parameters.AddWithValue("@d12", row.Cells(10).Value)
490 cmd.Parameters.AddWithValue("@d13", row.Cells(11).Value)
491 cmd.Parameters.AddWithValue("@d14", row.Cells(12).Value)
492 cmd.ExecuteNonQuery()
493 cmd.Parameters.Clear()
494 End If
495 Next
496 con.Close()
497 End If
498
499 con = New SqlConnection(cs)
500 con.Open()
501 Dim cb2 As String = "insert into Invoice1_Payment(InvoiceID,PaymentMode,TotalPaid,PaymentDate) VALUES (" & txtID.Text & " ,@d4,@d5,@d6)"
502 cmd = New SqlCommand(cb2)
503 cmd.Connection = con
504 ' Prepare command for repeated execution
505 cmd.Prepare()
506 ' Data to be inserted
507 For Each row As DataGridViewRow In DataGridView2.Rows
508 If Not row.IsNewRow Then
509 cmd.Parameters.AddWithValue("@d4", row.Cells(0).Value)
510 cmd.Parameters.AddWithValue("@d5", row.Cells(1).Value)
511 cmd.Parameters.AddWithValue("@d6", row.Cells(2).Value)
512 cmd.ExecuteNonQuery()
513 cmd.Parameters.Clear()
514 End If
515 Next
516 con.Close()
517 If DataGridView1.Rows.Count <> 0 Then
518 For Each row As DataGridViewRow In DataGridView1.Rows
519 If Not row.IsNewRow Then
520 con = New SqlConnection(cs)
521 con.Open()
522 Dim cb4 As String = "update Temp_stock set qty = qty - (" & row.Cells(5).Value & ") where ProductID=@d1"
523 cmd = New SqlCommand(cb4)
524 cmd.Connection = con
525 cmd.Parameters.AddWithValue("@d1", row.Cells(12).Value)
526 cmd.ExecuteNonQuery()
527 con.Close()
528 End If
529 Next
530 End If
531 con.Close()
532 Dim st As String = "added the bill (Products + Service) order having Invoice no. '" & txtInvoiceNo.Text & "'"
533 LogFunc(lblUser.Text, st)
534 If CheckForInternetConnection() = True Then
535 con = New SqlConnection(cs)
536 con.Open()
537 Dim ctn As String = "select RTRIM(APIURL) from SMSSetting where IsDefault='Yes' and IsEnabled='Yes'"
538 cmd = New SqlCommand(ctn)
539 cmd.Connection = con
540 rdr = cmd.ExecuteReader()
541 If rdr.Read() Then
542 st2 = rdr.GetValue(0)
543 Dim st3 As String = "Hello, " & txtCustomerName.Text & " you have successfully received your item having invoice no. " & txtInvoiceNo.Text & ""
544 SMSFunc(txtContactNo.Text, st3, st2)
545 If (rdr IsNot Nothing) Then
546 rdr.Close()
547 End If
548 End If
549 End If
550 con.Close()
551 btnSave.Enabled = False
552 RefreshRecords()
553 MessageBox.Show("Successfully done", "Billing", MessageBoxButtons.OK, MessageBoxIcon.Information)
554 Print()
555 Catch ex As Exception
556 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
557 End Try
558 End Sub
559
560 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
561 If Len(Trim(txtCustomerName.Text)) = 0 Then
562 MessageBox.Show("Please retrieve service details", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
563 Exit Sub
564 End If
565 If Len(Trim(txtRepairCharges.Text)) = 0 Then
566 MessageBox.Show("Please enter service charges", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
567 txtRepairCharges.Focus()
568 Exit Sub
569 End If
570 If Len(Trim(txtServiceTaxPer.Text)) = 0 Then
571 MessageBox.Show("Please enter service tax %", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
572 txtServiceTaxPer.Focus()
573 Exit Sub
574 End If
575 If DataGridView2.Rows.Count = 0 Then
576 MessageBox.Show("sorry no payment info added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
577 Exit Sub
578 End If
579 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
580 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
581 Exit Sub
582 End If
583 Try
584 con = New SqlConnection(cs)
585 con.Open()
586 Dim cb As String = "Update InvoiceInfo1 set InvoiceNo=@d2, InvoiceDate=@d3, ServiceID=@d4, RepairCharges=@d5, Upfront=@d6, ProductCharges=@d7, ServiceTaxPer=@d8, ServiceTax=@d9, GrandTotal=@d10, TotalPaid=@d11, Balance=@d12, Remarks=@d13 where Inv_ID=@d1"
587 cmd = New SqlCommand(cb)
588 cmd.Parameters.AddWithValue("@d1", txtID.Text)
589 cmd.Parameters.AddWithValue("@d2", txtInvoiceNo.Text)
590 cmd.Parameters.AddWithValue("@d3", dtpInvoiceDate.Value.Date)
591 cmd.Parameters.AddWithValue("@d4", txtS_ID.Text)
592 cmd.Parameters.AddWithValue("@d5", txtRepairCharges.Text)
593 cmd.Parameters.AddWithValue("@d6", txtUpfront.Text)
594 cmd.Parameters.AddWithValue("@d7", txtProductCharges.Text)
595 cmd.Parameters.AddWithValue("@d8", txtServiceTaxPer.Text)
596 cmd.Parameters.AddWithValue("@d9", txtServiceTaxAmount.Text)
597 cmd.Parameters.AddWithValue("@d10", txtGrandTotal.Text)
598 cmd.Parameters.AddWithValue("@d11", txtTotalPayment.Text)
599 cmd.Parameters.AddWithValue("@d12", txtPaymentDue.Text)
600 cmd.Parameters.AddWithValue("@d13", txtRemarks.Text)
601 cmd.Connection = con
602 cmd.ExecuteReader()
603 con.Close()
604 con = New SqlConnection(cs)
605 con.Open()
606 Dim cq As String = "delete from Invoice1_Payment where InvoiceID=@d1"
607 cmd = New SqlCommand(cq)
608 cmd.Parameters.AddWithValue("@d1", txtID.Text)
609 cmd.Connection = con
610 cmd.ExecuteNonQuery()
611 con.Close()
612 con = New SqlConnection(cs)
613 con.Open()
614 Dim cb2 As String = "insert into Invoice1_Payment(InvoiceID,PaymentMode,TotalPaid,PaymentDate) VALUES (" & txtID.Text & " ,@d4,@d5,@d6)"
615 cmd = New SqlCommand(cb2)
616 cmd.Connection = con
617 ' Prepare command for repeated execution
618 cmd.Prepare()
619 ' Data to be inserted
620 For Each row As DataGridViewRow In DataGridView2.Rows
621 If Not row.IsNewRow Then
622 cmd.Parameters.AddWithValue("@d4", row.Cells(0).Value)
623 cmd.Parameters.AddWithValue("@d5", row.Cells(1).Value)
624 cmd.Parameters.AddWithValue("@d6", row.Cells(2).Value)
625 cmd.ExecuteNonQuery()
626 cmd.Parameters.Clear()
627 End If
628 Next
629 con.Close()
630 Dim st As String = "updated the bill (Products + Service) having invoice no. '" & txtInvoiceNo.Text & "'"
631 LogFunc(lblUser.Text, st)
632 btnUpdate.Enabled = False
633 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
634 Catch ex As Exception
635 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
636 End Try
637 End Sub
638
639 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
640 frmBillingRecord1.lblSet.Text = "Billing"
641 frmBillingRecord1.Reset()
642 frmBillingRecord1.ShowDialog()
643 End Sub
644
645 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
646 Reset()
647 Reset()
648 End Sub
649
650
651 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
652 Cursor = Cursors.Default
653 Timer1.Enabled = False
654 End Sub
655
656 Private Sub btnPrint_Click(sender As System.Object, e As System.EventArgs) Handles btnPrint.Click
657 Print()
658 End Sub
659
660 Private Sub btnAdd1_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd1.Click
661 Try
662 If cmbPaymentMode.Text = "" Then
663 MessageBox.Show("Please select payment mode", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
664 cmbPaymentMode.Focus()
665 Exit Sub
666 End If
667 If txtPayment.Text = "" Then
668 MessageBox.Show("Please enter payment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
669 txtPayment.Focus()
670 Exit Sub
671 End If
672 DataGridView2.Rows.Add(cmbPaymentMode.Text, txtPayment.Text, dtpPaymentDate.Value.Date)
673 Dim j As Double = 0
674 j = TotalPayment()
675 j = Math.Round(j, 2)
676 txtTotalPayment.Text = j
677 Compute1()
678 Clear1()
679 Catch ex As Exception
680 MsgBox(ex.Message)
681 End Try
682 End Sub
683 Sub Clear1()
684 cmbPaymentMode.SelectedIndex = -1
685 txtPayment.Text = ""
686 dtpPaymentDate.Text = Today
687 btnAdd1.Enabled = True
688 btnRemove1.Enabled = False
689 btnListUpdate1.Enabled = False
690 End Sub
691 Private Sub btnRemove1_Click(sender As System.Object, e As System.EventArgs) Handles btnRemove1.Click
692 Try
693 For Each row As DataGridViewRow In DataGridView2.SelectedRows
694 DataGridView2.Rows.Remove(row)
695 Next
696 Dim k As Double = 0
697 k = TotalPayment()
698 k = Math.Round(k, 2)
699 txtTotalPayment.Text = k
700 Compute1()
701 Compute()
702 Clear1()
703 Catch ex As Exception
704 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
705 End Try
706 End Sub
707
708 Private Sub txtPayment_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtPayment.KeyPress
709 Dim keyChar = e.KeyChar
710
711 If Char.IsControl(keyChar) Then
712 'Allow all control characters.
713 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
714 Dim text = Me.txtPayment.Text
715 Dim selectionStart = Me.txtPayment.SelectionStart
716 Dim selectionLength = Me.txtPayment.SelectionLength
717
718 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
719
720 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
721 'Reject an integer that is longer than 16 digits.
722 e.Handled = True
723 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
724 'Reject a real number with two many decimal places.
725 e.Handled = False
726 End If
727 Else
728 'Reject all other characters.
729 e.Handled = True
730 End If
731 End Sub
732
733 Private Sub DataGridView2_MouseClick(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView2.MouseClick
734 btnRemove1.Enabled = True
735 If (Me.DataGridView2.Rows.Count > 0) Then
736 Me.btnRemove1.Enabled = True
737 Me.btnListUpdate1.Enabled = True
738 Me.btnAdd1.Enabled = False
739 Dim row As DataGridViewRow = Me.DataGridView2.SelectedRows.Item(0)
740 Me.cmbPaymentMode.Text = (row.Cells.Item(0).Value)
741 Me.txtPayment.Text = (row.Cells.Item(1).Value)
742 Me.dtpPaymentDate.Text = (row.Cells.Item(2).Value)
743 End If
744 End Sub
745
746 Private Sub btnListReset1_Click(sender As System.Object, e As System.EventArgs) Handles btnListReset1.Click
747 Clear1()
748 End Sub
749
750 Private Sub btnListReset_Click(sender As System.Object, e As System.EventArgs) Handles btnListReset.Click
751 Clear()
752 End Sub
753
754 Private Sub DataGridView2_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView2.RowPostPaint
755 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
756 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
757 If DataGridView2.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
758 DataGridView2.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
759 End If
760 Dim b As Brush = SystemBrushes.ControlText
761 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
762
763 End Sub
764
765 Private Sub btnListUpdate1_Click(sender As System.Object, e As System.EventArgs) Handles btnListUpdate1.Click
766 Try
767 If cmbPaymentMode.Text = "" Then
768 MessageBox.Show("Please select payment mode", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
769 cmbPaymentMode.Focus()
770 Exit Sub
771 End If
772 If txtPayment.Text = "" Then
773 MessageBox.Show("Please enter payment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
774 txtPayment.Focus()
775 Exit Sub
776 End If
777 For Each row As DataGridViewRow In DataGridView2.SelectedRows
778 DataGridView2.Rows.Remove(row)
779 Next
780 DataGridView2.Rows.Add(cmbPaymentMode.Text, txtPayment.Text, dtpPaymentDate.Value.Date)
781 Dim j As Double = 0
782 j = TotalPayment()
783 j = Math.Round(j, 2)
784 txtTotalPayment.Text = j
785 Compute1()
786 Clear1()
787 Catch ex As Exception
788 MsgBox(ex.Message)
789 End Try
790 End Sub
791
792 Private Sub btnListUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnListUpdate.Click
793 Try
794 If txtProductCode.Text = "" Then
795 MessageBox.Show("Please retrieve product code", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
796 txtProductCode.Focus()
797 Exit Sub
798 End If
799 If txtQty.Text = "" Then
800 MessageBox.Show("Please enter quantity", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
801 txtQty.Focus()
802 Exit Sub
803 End If
804 If txtQty.Text = 0 Then
805 MessageBox.Show("Quantity can not be zero", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
806 txtQty.Focus()
807 Exit Sub
808 End If
809
810 For Each row As DataGridViewRow In DataGridView1.SelectedRows
811 DataGridView1.Rows.Remove(row)
812 Next
813 DataGridView1.Rows.Add(txtProductCode.Text, txtProductName.Text, txtCostPrice.Text, txtSellingPrice.Text, txtMargin.Text, txtQty.Text, txtAmount.Text, txtDiscountPer.Text, txtDiscountAmount.Text, txtVAT.Text, txtVATAmount.Text, txtTotalAmount.Text, txtProductID.Text)
814 Dim k As Double = 0
815 k = GrandTotal()
816 k = Math.Round(k, 2)
817 txtGrandTotal.Text = k
818 Compute1()
819 Clear()
820 Catch ex As Exception
821 MsgBox(ex.Message)
822 End Try
823 End Sub
824
825 Private Sub txtRepairCharges_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtRepairCharges.KeyPress
826 Dim keyChar = e.KeyChar
827
828 If Char.IsControl(keyChar) Then
829 'Allow all control characters.
830 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
831 Dim text = Me.txtRepairCharges.Text
832 Dim selectionStart = Me.txtRepairCharges.SelectionStart
833 Dim selectionLength = Me.txtRepairCharges.SelectionLength
834
835 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
836
837 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
838 'Reject an integer that is longer than 16 digits.
839 e.Handled = True
840 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
841 'Reject a real number with two many decimal places.
842 e.Handled = False
843 End If
844 Else
845 'Reject all other characters.
846 e.Handled = True
847 End If
848 End Sub
849
850 Private Sub txtServiceTaxPer_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtServiceTaxPer.KeyPress
851 Dim keyChar = e.KeyChar
852
853 If Char.IsControl(keyChar) Then
854 'Allow all control characters.
855 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
856 Dim text = Me.txtServiceTaxPer.Text
857 Dim selectionStart = Me.txtServiceTaxPer.SelectionStart
858 Dim selectionLength = Me.txtServiceTaxPer.SelectionLength
859
860 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
861
862 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
863 'Reject an integer that is longer than 16 digits.
864 e.Handled = True
865 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
866 'Reject a real number with two many decimal places.
867 e.Handled = False
868 End If
869 Else
870 'Reject all other characters.
871 e.Handled = True
872 End If
873 End Sub
874
875 Private Sub txtRepairCharges_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtRepairCharges.TextChanged
876 Compute1()
877 End Sub
878
879 Private Sub txtServiceTaxPer_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtServiceTaxPer.TextChanged
880 Compute1()
881 End Sub
882 End Class
